package pro.jiug.kExcel.utils;


import pro.jiug.kExcel.utils.IdWorker;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;

@Component
public class InsertISO {



//    private String url = "jdbc:mysql://localhost:3306/jxxh";
//    private  String username = "root";
//    private  String password = "root";

    private static Properties properties_jdbc;

    private static  String driver ="com.mysql.jdbc.Driver" ;
    private static  String url ="jdbc:mysql://localhost:3306/keffett?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8";
    private static  String username ="root";
    private static  String password ="root";


//    static {
//        properties_jdbc = new Properties();
//
//        InputStream fastdfs = FastDfsUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
//        try {
//            properties_jdbc.load(fastdfs);
//            driver = properties_jdbc.getProperty("jdbc.driverClass");
//            url = properties_jdbc.getProperty("jdbc.url");
//            username = properties_jdbc.getProperty("jdbc.username");
//            password = properties_jdbc.getProperty("jdbc.password");
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }

    public void insertList(List<List<String>> list ,String tableName  ) {
        Connection conn = null;
        PreparedStatement pstm = null;
//        List<int[]> list = CalcUtil.calcAllCombination();
        try {
            Class.forName(driver);
            //String url="jdbc:mysql://127.0.0.1:3306/jxxh2";
            tableName="hymax_dzts";

            conn = DriverManager.getConnection(url, username, password);
            StringBuffer sql = new StringBuffer("INSERT INTO " + tableName + " VALUES ");
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();

            System.out.println("数据大小：" + list.size());

            int num = 0;
			for (int i = 0; i < list.size(); i++) {
                num++;
                List<String> entryList = list.get(i);
                if (i != 0) {
                    sql.append(",");
                }
                sql.append("(");
                for(int j = 0; j<entryList.size(); j++){
                    if(entryList.get(j)==null){
//                        break;
                        sql.append("'',");
                        continue;
                    }
                    if(j == 0){
                        long lid = IdWorker.idWork.nextId();
                        sql.append("'"+ lid +"'");
                    }else{
                        if(entryList.get(j)!=null){
                            entryList.get(j).replace("'","\'");
                            sql.append("'"+ entryList.get(j) +"'");
                        }else{
                            sql.append("''");
                        }
                    }
                    sql.append(",");
                }

                sql.deleteCharAt(sql.length() - 1);
                sql.append(")");
                if (num == 50000) {
                    pstm = conn.prepareStatement(sql.toString().replace("VALUES ,", "VALUES"));
                    pstm.executeUpdate();
                    conn.commit();
                    sql.setLength(0);
                    sql.append("INSERT INTO " + tableName + " VALUES ");
                    num = 0;
                }
            }
            pstm = conn.prepareStatement(sql.toString().replace("VALUES ,", "VALUES"));
            pstm.executeUpdate();
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime) / 1000 + "秒");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            if (pstm != null) {
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }


    public void editList(List<List<String>> list ,String tableName,String tiaojianCol,String editCol) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            StringBuffer sql = new StringBuffer("update " + tableName + " set ");
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();

            System.out.println("数据大小：" + list.size());


            String[] tiaojiaColArr = tiaojianCol.split(",");
            String[] editColArr = editCol.split(",");
            for (int i = 0; i < list.size(); i++) {

                List<String> entryList = list.get(i);

                //===开启查询是否有此条记录===========
                StringBuffer sqlcx =  new StringBuffer("select *  FROM "+tableName+" where 1=1" );
                for (int a=0;a<tiaojiaColArr.length;a++) {
                    String[] tjsplit = tiaojiaColArr[a].split(":");
                    sqlcx.append(" AND ");
                    sqlcx.append(tjsplit[0]).append("=").append("'" + entryList.get(Integer.parseInt(tjsplit[1])) + "'");
                }
                pstm = conn.prepareStatement(sqlcx.toString());
                ResultSet rs=pstm.executeQuery();
                if(!rs.next()){
                    long lid = IdWorker.idWork.nextId();
                    StringBuffer sqlInsert1 = new StringBuffer("INSERT INTO " + tableName + " ( id ,");
                    StringBuffer sqlInsert2 = new StringBuffer(") VALUES (" + "'"+ lid +"'," );


                    for (int a=0;a<tiaojiaColArr.length;a++) {
                        String[] tjsplit = tiaojiaColArr[a].split(":");
                        sqlInsert1.append(tjsplit[0]).append(",");
                        sqlInsert2.append("'" + entryList.get(Integer.parseInt(tjsplit[1])) + "'").append(",");
                    }

                    for (int a=0;a<editColArr.length;a++) {
                        String[] tjsplit = editColArr[a].split(":");
                        if(a>=editColArr.length-1){
                            sqlInsert1.append(tjsplit[0]);
                            sqlInsert2.append("'" + entryList.get(Integer.parseInt(tjsplit[1])) + "'");
                        }else{
                            sqlInsert1.append(tjsplit[0]).append(",");
                            sqlInsert2.append("'" + entryList.get(Integer.parseInt(tjsplit[1])) + "'").append(",");
                        }
                    }
                    sqlInsert1.append(sqlInsert2).append(")");
                    StringBuffer sqlInsert = sqlInsert1;

                    System.out.println(sqlInsert);
                    pstm = conn.prepareStatement(sqlInsert.toString());
                    pstm.executeUpdate();
                    conn.commit();
                    System.out.print("数据导入成功>>>");
                    System.out.println(entryList);
                }else{
                    sql = new StringBuffer("update " + tableName + " set ");

                    for (int a=0;a<editColArr.length;a++) {
                        String[] esplit = editColArr[a].split(":");
                        if(a >= editColArr.length - 1){
                            sql.append(esplit[0]).append("=").append(entryList.get(Integer.parseInt(esplit[1])));
                        }else{
                            sql.append(esplit[0]).append("=").append(entryList.get(Integer.parseInt(esplit[1]))).append(",");
                        }
                    }
                    sql.append(" where 1=1  ");
                    for (int a=0;a<tiaojiaColArr.length;a++) {
                        String[] tjsplit = tiaojiaColArr[a].split(":");
                        sql.append(" AND ");
                        sql.append(tjsplit[0]).append("=").append("'" + entryList.get(Integer.parseInt(tjsplit[1])) + "'");
                    }
                    System.out.println(sql);
                    pstm = conn.prepareStatement(sql.toString());
                    pstm.executeUpdate();
                    conn.commit();
                    System.out.print("数据修改成功>>>");
                    System.out.println(entryList);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            if (pstm != null) {
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }
}
